package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.pageModel.DeptInfo;
import sy.service.InfoServiceI;
import sy.util.StringUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016-06-29.
 */
@Service
public class InfoServiceImpl implements InfoServiceI {
    @Autowired
    private JdbcTemplate template;
    @Override
    public List<BaseEntity> findList(String id) {
        String sql="select *from p_info where 1=1";
        id=id==null?"":id;
        String ids[]=id.split(",");
        for (int i = 0; i < ids.length; i++) {
            if (i==0&& ids.length==1) sql += " and mainid='" + ids[i] + "'";
            if (ids.length>1&& i==0) sql+=" and ( ";
            if (i==0&& ids.length!=1)sql+=" mainid='"+ids[i]+"'";
            if (i!=0)sql+=" or mainid='"+ids[i]+"'";
            if (ids.length==i+1&&ids.length>1)sql+=")";

        }
        final List<BaseEntity> list=new ArrayList<>();
        template.query(sql, new Object[]{}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                BaseEntity entity=new BaseEntity();
                entity.setId(rs.getString("id"));
                entity.setText(rs.getString("name"));
                list.add(entity);
            }
        });
        return list;
    }
    @Override
    public DataGrid detagrid(DeptInfo info) {
        DataGrid dg = new DataGrid();
        String sql = "select count(*) from p_info pi where 1=1  ";
        sql = addWhere(info, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = "SELECT *from p_info pi,u_dept_type dt,u_dept ud where pi.mainid=dt.id and pi.deptid=ud.id ";
        sql = addWhere(info, sql);
        sql = addOrderBy(info, sql);
        sql = addLimit(info, sql);
        final List<DeptInfo> infoList = new ArrayList<DeptInfo>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                DeptInfo di=new DeptInfo();
                di.setId(rs.getString("pi.id"));
                di.setDeptId(rs.getString("ud.id"));
                di.setDeptName(rs.getString("ud.depts"));
                di.setTypeId(rs.getString("dt.id"));
                di.setTypeName(rs.getString("dt.names"));
                di.setDescs(rs.getString("pi.descs"));
                di.setPic(rs.getString("pi.pic1"));
                di.setName(rs.getString("pi.name"));
                di.setLytsId(rs.getString("pi.lytsid"));
                di.setHjtsId(rs.getString("pi.hjtsid"));
                di.setIsShow(rs.getString("pi.competitive_products"));
                di.setOrder(rs.getString("pi.orderid"));
                di.setAttention(rs.getString("pi.attention"));
                infoList.add(di);
            }});
        dg.setRows(infoList);
        return dg;
    }

    @Override
    @Transactional
    public int addInfoService(DeptInfo info) throws Exception {
        String sql="INSERT INTO p_info (id, deptid, mainid,  descs, pic1, name, hjtsid, lytsid, competitive_products, orderid, attention,subid,snums,rnums) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int rows=template.update(sql,new Object[]{info.getId(),info.getDeptId(),info.getTypeId(),info.getDescs(),info.getPic(),info.getName(),info.getHjtsId(),info.getLytsId(),info.getIsShow(),info.getOrder(),info.getAttention(),"",0,0});
        return rows;
    }

    @Override
    @Transactional
    public int editInfoService(DeptInfo info) throws Exception{
        String sql="UPDATE p_info SET deptid=?, mainid=?,  descs=?, pic1=?, name=?, hjtsid=?, lytsid=?, competitive_products=?, orderid=?, attention=? WHERE id=?";
        int rows=template.update(sql,new Object[]{info.getDeptId(),info.getTypeId(),info.getDescs(),info.getPic(),info.getName(),info.getHjtsId(),info.getLytsId(),info.getIsShow(),info.getOrder(),info.getAttention(),info.getId()});
        return rows;
    }

    @Override
    @Transactional
    public int deleteInfoService(DeptInfo info) throws Exception{
        String sql="DELETE FROM p_info where id=?";
        int rows=0;
        String ids[]=info.getIds().split(",");
        for (int i = 0; i <ids.length ; i++) {
            rows=template.update(sql,new Object[]{ids[i]});
        }
        return rows;
    }

    private String addLimit(DeptInfo info, String sql) {
        int fromRow = (info.getPage() - 1) * info.getRows();
        sql += " limit " + fromRow + "," + info.getRows();
        return sql;
    }

    private String addWhere(DeptInfo info, String sql) {
        if(!StringUtil.isNullOrEmpty(info.getName())) {
            sql += " and pi.name like '%" + info.getName() + "%'";
        }
        if(!StringUtil.isNullOrEmpty(info.getTypeId())) {
            sql += " and pi.mainid like '%" + info.getTypeId() + "%'";
        }
        if(!StringUtil.isNullOrEmpty(info.getCreateTimeStart())) {
            sql += " and date_time >= '" + info.getCreateTimeStart() + "'";
        }
        if(!StringUtil.isNullOrEmpty(info.getCreateTimeEnd())) {
            sql += " and date_time <= '" + info.getCreateTimeEnd() + "'";
        }
        return sql;
    }


    private String addOrderBy(DeptInfo info, String sql) {
        if (info.getSort() != null) {
            if("id".equals(info.getSort()))info.setSort("pi.id");
            if("showName".equals(info.getSort()))info.setSort("pi.competitive_products");
            if("deptName".equals(info.getSort())) info.setSort("pi.deptid");
            if("typeName".equals(info.getSort())) info.setSort("pi.mainid");
            sql += " order by " + info.getSort() + " " + info.getOrder();
        }
        return sql;
    }
}
